Looping through xml and store data to MySQL table

Ask Time：2018-05-09T13:56:57 Author：Abdul Rahman

XML Output is as follows

<?xml version="1.0" encoding="UTF-8"?>
<Country code="GR">

   <Regions>
      <Region translation="null">Athens Airport</Region>
      <Region translation="null">Athens Coast</Region>
      <Region translation="null">Athens Suburbs-Attica</Region>
      <Region translation="null">Athens</Region>
      <Region translation="null">Central Greece-Etoloakarnania</Region>
      <Region translation="null">Central Greece-Evritania</Region>
      <Region translation="null">Central Greece-Ioannina</Region>
      <Region translation="null">Central Greece-Karditsa</Region>
      <Region translation="null">Central Greece-Larissa</Region>
      <Region translation="null">Central Greece-Magnissia</Region>
   </Regions>
</Country>

Each Region has cities and as follows

<?xml version="1.0" encoding="UTF-8"?>
<Country code="GR">
   <Cities>
      <City translation="null">Acharnes</City>
      <City translation="null">Achladies</City>
      <City translation="null">Achladochori</City>
      <City translation="null">Adamas</City>
      <City translation="null">Afandou</City>
      <City translation="null">Afiartis</City>
      <City translation="null">Agali</City>
      <City translation="null">Aghia Anna</City>
      <City translation="null">Aghia Paraskevi</City>
</Cities>

What I need is to insert all the cities under every region and country to a table. A country has many region and a region has multiple cities. What I tried is

$regions = array("GR" => "Greece", "BR" => "Brazil", "US" => "USA");

foreach ($regions as $code => $country) {

    $url = "URL which gives an xml output"
    file_put_contents($code . '.xml', file_get_contents($url));

    $xml = simplexml_load_file($code".xml") or die("Error: Cannot create object");

    foreach ($xml->children() as $row) {
        $region = $row->Region;
    }
}

How can I loop through and save it in mysql..? TIA

Author:Abdul Rahman，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/50246405/looping-through-xml-and-store-data-to-mysql-table

Sohrab Yousefi :

If your .XML output is like this:\n\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<Country>\n <Code></Code>\n <Regions>\n <Region>\n <Name></Name>\n <Cities>\n <City></City>\n <City></City>\n <City></City>\n </Cities>\n </Region>\n <Region>\n <Name></Name>\n <Cities>\n <City></City>\n <City></City>\n <City></City>\n </Cities>\n </Region>\n </Regions>\n</Country>\n<Country>\n <Code></Code>\n <Regions>\n <Region>\n <Name></Name>\n <Cities>\n <City></City>\n <City></City>\n <City></City>\n </Cities>\n </Region>\n <Region>\n <Name></Name>\n <Cities>\n <City></City>\n <City></City>\n <City></City>\n </Cities>\n </Region>\n </Regions>\n</Country>\n\n\nYou should do this in your php:\n\n$url = \"URL which gives an xml output\";\n$xml = new SimpleXMLElement($url);\nforeach($xml->children() as $country){\n // Query to insert the country into the countries table by $country->Code\n foreach($country->Regions as $region){\n // Query to insert the region into the regions table by $country->Code and $region->Name\n foreach($region->Cities as $city){\n // Query to insert the city into the cities table by $country->Code and $region->Name and $city->City\n }\n }\n}\n",

2018-05-09T08:19:38

Looping through xml and store data to MySQL table